package spring.jdbc.cases;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.aspectj.lang.annotation.After;
import org.aspectj.lang.annotation.Before;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.testng.Assert;
import org.testng.annotations.BeforeClass;
import org.testng.annotations.Test;

public class JdbcTemplateTest2 {

    private static JdbcTemplate jdbcTemplate;

    @BeforeClass
    public static void setUpClass() {
        String url = "jdbc:hsqldb:mem:test";
        String username = "root";
        String password = "root";
        DriverManagerDataSource dataSource = new DriverManagerDataSource(url, username, password);
        dataSource.setDriverClassName("org.hsqldb.jdbcDriver");
        jdbcTemplate = new JdbcTemplate(dataSource);
    }

    @Before("")
    public void setUp() {
        String createTableSql = "create memory table test" + ""
                + "(id int generated by default as identity primary key," + " name varchar(100) )";
        jdbcTemplate.update(createTableSql);
    }

    @After("")
    public void tearDown() {
        String dropTableSql = "drop table test";
        jdbcTemplate.execute(dropTableSql);
    }

    @Test
    public void testCURD() {
        insert();
        update();
        delete();
        select();
    }

    private void delete() {
        jdbcTemplate.update("delete from test where  name=?", "name2");
        jdbcTemplate.execute("select count(*) from test");
    }

    private void update() {
        jdbcTemplate.update("update test set name='name3' where name =?", "name1");
        jdbcTemplate.execute("select count(*) from test where name='name3' ");

    }

    private void insert() {
        jdbcTemplate.update("insert into test(name) values ('name1')");
        jdbcTemplate.update("insert into test(name) values ('name2')");
        jdbcTemplate.execute("select count(*) from test");

    }

    private void select() {
        jdbcTemplate.query("select * from test", new RowCallbackHandler() {

            @Override
            public void processRow(ResultSet rs) throws SQLException {
                System.out.println("id:----" + rs.getInt("id"));
                System.out.println("name" + rs.getString("name"));

            }

        });
    }

    @Test
    public void testPreparedStatement1() {
        int count = jdbcTemplate.execute(new PreparedStatementCreator() {

            @Override
            public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {

                return conn.prepareStatement("Select count(*) from test");
            }
        }, new PreparedStatementCallback<Integer>() {

            @Override
            public Integer doInPreparedStatement(PreparedStatement pstmt) throws SQLException, DataAccessException {
                pstmt.execute();
                ResultSet rs = pstmt.getResultSet();
                rs.next();
                return rs.getInt(1);
            }
        });
        System.out.println(count + "---count");
    }

    @Test
    public void testPreparedStatement2() {
        String insertSql = "insert into test(name) values (?)";
        int count = jdbcTemplate.update(insertSql, new PreparedStatementSetter() {

            @Override
            public void setValues(PreparedStatement pstmt) throws SQLException {
                pstmt.setObject(1, "name4");

            }

        });
        Assert.assertEquals(1, count);
        String deleteSql = "delete from test where name=?";
        count = jdbcTemplate.update(deleteSql, "name4");
        Assert.assertEquals(1, count);
    }

    @Test
    public void testResultSet() {
        jdbcTemplate.update("insert into test(name) values ('name5')");
        String listSql = "select * from test";
        List result = jdbcTemplate.query(listSql, new RowMapper<Map>() {

            @Override
            public Map mapRow(ResultSet rs, int rowNum) throws SQLException {
                Map row = new HashMap();
                row.put(rs.getInt("id"), rs.getString("name"));

                return row;
            }
        });
        Assert.assertEquals(1, result.size());
        jdbcTemplate.update("delete from test where name='name5'");
    }

    @Test
    public void testResultSet2() {
        jdbcTemplate.update("insert into test(name) values ('name5')");
        String listSql = "select * from test";
        final List result = new ArrayList();
        jdbcTemplate.query(listSql, new RowCallbackHandler() {

            @Override
            public void processRow(ResultSet rs) throws SQLException {
                Map row = new HashMap();
                row.put(rs.getInt("id"), rs.getString("name"));
                result.add(row);

            }

        });
        Assert.assertEquals(1, result.size());

    }

    @Test
    public void testResultSet3() {
        jdbcTemplate.update("insert into test(name) values('name5')");
        String listSql = "select * from test";
        List<?> result = jdbcTemplate.query(listSql, new ResultSetExtractor<List<?>>() {

            @Override
            public List<Map<Integer, String>> extractData(ResultSet rs) throws SQLException, DataAccessException {
                List<Map<Integer, String>> result = new ArrayList();
                while (rs.next()) {
                    Map<Integer, String> row = new HashMap<Integer, String>();
                    row.put(rs.getInt("id"), rs.getString("name"));
                    result.add(row);

                }
                return result;

            }

        });
        Assert.assertEquals(0, result.size());
        jdbcTemplate.update("delete from test where name='name5'");
    }

}
